﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using DTO;

namespace DAO
{
    public class DuLieuDAO : DataProvider
    {
        protected override object GetDataRowFromDataReader(OleDbDataReader dr)
        {
            DuLieuDTO duLieuDTO = new DuLieuDTO();
            if (!dr.IsDBNull(0))
            {
                duLieuDTO.MaDuLieu = dr.GetInt32(0);
            }

            if (!dr.IsDBNull(1))
            {
                duLieuDTO.MaDichVu = dr.GetInt32(1);
            }

            if (!dr.IsDBNull(2))
            {
                duLieuDTO.MaTenDiaDiem = dr.GetInt32(2);
            }

            if (!dr.IsDBNull(3))
            {
                duLieuDTO.SoNha = dr.GetString(3);
            }

            if (!dr.IsDBNull(4))
            {
                duLieuDTO.MaDuong = dr.GetInt32(4);
            }

            if (!dr.IsDBNull(5))
            {
                duLieuDTO.MaPhuong = dr.GetInt32(5);
            }

            if (!dr.IsDBNull(6))
            {
                duLieuDTO.MaQuanHuyen = dr.GetInt32(6);
            }

            if (!dr.IsDBNull(7))
            {
                duLieuDTO.MaTinhThanh = dr.GetInt32(7);
            }

            if (!dr.IsDBNull(8))
            {
                duLieuDTO.KinhDo = dr.GetDouble(8);
            }

            if (!dr.IsDBNull(9))
            {
                duLieuDTO.ViDo = dr.GetDouble(9);
            }

            if (!dr.IsDBNull(10))
            {
                duLieuDTO.ChuThich = dr.GetString(10);
            }

            return (object)duLieuDTO;
        }

        public List<DuLieuDTO> SearchWithSoNha(string strInput)
        {
            List<DuLieuDTO> lstDuLieuDTO = new List<DuLieuDTO>();
            try
            {
                strInput = strInput.Replace(@"'", @"''");
                //string strSQL = @"SELECT *" +
                //                @" FROM DULIEU" +
                //                @" WHERE '" + strInput + @"' LIKE " + @"'*' + SoNha + '*'";
                string strSQL = @"SELECT * FROM DULIEU WHERE INSTR('" + strInput + @"', SoNha) > 0";

                using (OleDbDataReader dr = this.ExcuteReader(strSQL))
                {
                    DuLieuDTO duLieuDTO = null;
                    while (dr.Read())
                    {
                        duLieuDTO = (DuLieuDTO)GetDataRowFromDataReader(dr);
                        lstDuLieuDTO.Add(duLieuDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                lstDuLieuDTO = new List<DuLieuDTO>();
            }

            return lstDuLieuDTO;
        }

        public List<DuLieuDTO> Search(string soNha, int maDichVu, int maTinhThanh, int maQuanHuyen, int maPhuong, int maDuong, int maTenDiaDiem)
        {
            List<DuLieuDTO> lstDuLieuDTO = new List<DuLieuDTO>();
            try
            {
                string strSQL = @"SELECT *" +
                                @" FROM DULIEU" +
                                @" WHERE";

                if (DataProvider._defaultNumber != maDichVu)
                {
                    strSQL += @" MaDichVu = " + maDichVu.ToString() + @" AND";
                }

                if (DataProvider._defaultNumber != maTenDiaDiem)
                {
                    strSQL += @" MaTenDiaDiem = " + maTenDiaDiem.ToString() + @" AND";
                }

                if (null != soNha && soNha.Length > 0)
                {
                    strSQL += @" SoNha = '" + soNha + @"' AND" ;
                }

                if (DataProvider._defaultNumber != maDuong)
                {
                    strSQL += @" MaDuong = " + maDuong.ToString() + @" AND";
                }

                if (DataProvider._defaultNumber != maPhuong)
                {
                    strSQL += @" MaPhuong = " + maPhuong.ToString() + @" AND";
                }

                if (DataProvider._defaultNumber != maQuanHuyen)
                {
                    strSQL += @" MaQuanHuyen = " + maQuanHuyen.ToString() + @" AND";
                }

                if (DataProvider._defaultNumber != maTinhThanh)
                {
                    strSQL += @" MaTinhThanh = " + maTinhThanh.ToString() + @" AND";
                }

                int posOfAnd = strSQL.LastIndexOf(" AND");
                strSQL = strSQL.Substring(0, posOfAnd);

                OleDbDataReader dr = this.ExcuteReader(strSQL);
                DuLieuDTO duLieuDTO = null;
                while (dr.Read())
                {
                    duLieuDTO = (DuLieuDTO)GetDataRowFromDataReader(dr);
                    lstDuLieuDTO.Add(duLieuDTO);
                }
            }
            catch (System.Exception ex)
            {
                lstDuLieuDTO = new List<DuLieuDTO>();
            }

            return lstDuLieuDTO;
        }
    }
}
